Declare @TodayDate DateTime, @EndDate DateTime
SET @TodayDate = '1-Jan-2015'
SET @EndDate = GETDATE()

Select * from tblDailyDetailReport_TEST Where StartDate Between @TodayDate And @EndDate

WHILE @TodayDate < @EndDate
BEGIN
	Declare @AAC1D int,@AAC1DReceived int, @AAC2D int, @DEC1D int,@DEC1DReceived int,@DEC2D int

	IF DATENAME(dw, @TodayDate) <> 'Sunday' 
	AND DATENAME(dw, @TodayDate) <> 'Saturday'
	And ((Select Count(*) from tblPublicHolidays Where Convert(varchar(8), HolidayDate, 112) = Convert(varchar(8), @TodayDate, 112)) = 0)
	BEGIN
		
		DECLARE @CutOffDateTime DATETIME,
			@StrDate VARCHAR(30),
			@StrTime VARCHAR(30)

		Declare @CutOffDate DateTime
		Declare @CutOffTime Time
		Declare @TempDate varchar(30)

		SET @CutOffDate = @TodayDate
		SET @CutOffTime = '6:00 PM'
		SET @TempDate = CONVERT(VARCHAR, @CutOffDate, 101);
		SET @StrTime = CONVERT(VARCHAR, @CutOffTime, 108);
		SET @StrDate = (@TempDate + ' ' + @StrTime);
		SET @CutOffDateTime = CONVERT(DATETIME,@StrDate,121);
		SET @TodayDate = @CutOffDateTime
	
		--Declare @TodayDate datetime
		Declare @LastWorkingDate datetime
		Declare @LastWorkingDateTNeg1 datetime
		--SET @TodayDate = '10-Jan-2014 5:00 PM'

		SET @LastWorkingDate = dbo.funcGetLastWorkingDateTime(@TodayDate)
		SET @LastWorkingDate = DateAdd(millisecond, 2, @LastWorkingDate)

		SET @LastWorkingDateTNeg1 = dbo.funcGetLastWorkingDateTime(@LastWorkingDate)
		SET @LastWorkingDateTNeg1 = DateAdd(millisecond, 2, @LastWorkingDateTNeg1)

		Select @LastWorkingDateTNeg1 BringForwardDate, @LastWorkingDate LastWorkingDate, @TodayDate TodayDate
		
		--AA "Received Yesterday"
		Select @AAC1DReceived = Count(CaseProfileIndex) From tblCaseProfileIndex 
		Where CaseSourceID = 1 And IsReplyCase = 0 And CreatedDate Between @LastWorkingDateTNeg1 And @LastWorkingDate

		--AA "Closed =< 1D"
		Select @AAC1D = Count(CaseProfileIndex) From tblCaseProfileIndex 
		Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus = 100 And ClosedOn Between @LastWorkingDateTNeg1 And @TodayDate And NoOfWorkingDays <= 1
		And CreatedDate Between @LastWorkingDateTNeg1 And @LastWorkingDate

		--AA "Closed =< 2D"
		Select @AAC2D = Count(CaseProfileIndex) From tblCaseProfileIndex
		Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus = 100 And ClosedOn Between @LastWorkingDate And @TodayDate And NoOfWorkingDays <= 2

		--DE "Received Yesterday "
		Select @DEC1DReceived = Count(CaseProfileIndex) From tblCaseProfileIndex 
		Where CaseSourceID = 2 And IsReplyCase = 0 And CreatedDate Between @LastWorkingDateTNeg1 And @LastWorkingDate

		--DE "Closed =< 1D "
		Select @DEC1D = Count(CaseProfileIndex) From tblCaseProfileIndex 
		Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus = 100 And ClosedOn Between @LastWorkingDateTNeg1 And @TodayDate And NoOfWorkingDays <= 1
		And CreatedDate Between @LastWorkingDateTNeg1 And @LastWorkingDate

		--DE "Closed =< 2D "
		Select @DEC2D = Count(CaseProfileIndex) From tblCaseProfileIndex 
		Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus = 100 And ClosedOn Between @LastWorkingDate And @TodayDate And NoOfWorkingDays <= 2

		Update tblDailyDetailReport_TEST 
		SET AAC1DReceived = @AAC1DReceived, AAC1D = @AAC1D, AAC2D = @AAC2D,
			DEC1DReceived = @DEC1DReceived, DEC1D = @DEC1D, DEC2D = @DEC2D
		Where Convert(varchar(8), EndDate, 112) = Convert(varchar(8), @TodayDate, 112)

	END

	SET @TodayDate = DateAdd(day, 1, @TodayDate)
END
